package com.rwy.dao;

import java.util.List;
import java.util.Map;

import com.rwy.dto.StatisticDeviceDTO;
import com.rwy.util.ObjectUtil;

import lombok.extern.log4j.Log4j;

@Log4j
public class StatisticsProvider {
	
	
	public String getPartnerIds(List<Integer> ids) {
		StringBuffer sb = new StringBuffer();
		for(int i = 0; i < ids.size(); i++) {
			sb.append(ids.get(i));
			if(i!=(ids.size() -1)) {
				sb.append(",");
			}
		}
		return sb.toString();
	}
	@SuppressWarnings("unchecked")
	public String getStatisticSummaryData(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		String startTime = (String) param.get("startTime");
		String endTime = (String) param.get("endTime");
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		String ids = getPartnerIds(partnerIds);
		
		sb.append("  SELECT  ");
		sb.append("   Round(convert(float,isnull(totalAmt, 0)) / 100, 2) AS totalAmt,  ");
		sb.append("   isnull(sheetCount, 0) AS sheetCount, ");
		sb.append("   isnull(deviceCount, 0 ) AS deviceCount,  ");
		sb.append("   isnull(printSuccNum, 0) AS printSuccNum, ");
		sb.append("   isnull(cancePrintNum, 0 )AS cancePrintNum, ");
		sb.append("   isnull(troubleDeviceNum, 0)AS troubleDeviceNum, ");
		sb.append("   Round(convert(float, isnull(refundAmt, 0)) / 100, 2) AS refundAmt ");
		sb.append("  FROM ( ");
		sb.append("  SELECT     ");
		
		sb.append("   (SELECT sum(TotalFee-totalRefundFee)   FROM Pay_PayOrder  ");
		sb.append("    WHERE PayOrderStatusID  = 102 ");
		sb.append("    AND BusinessPartnerID IN ("+ ids +")");
		sb.append("    AND TradeEndTime BETWEEN '"+ startTime+"' AND '"+endTime+"' ");
		sb.append("   ) AS totalAmt,                                                 ");
		
		sb.append("  ( SELECT sum(a.printcount) FROM Statistics_PrintRecord a            ");
		sb.append("    INNER JOIN CRM_SalesNetwork b    ");
		sb.append("    ON a.SalesNetworkID = b.SalesNetworkID  ");
		sb.append("    WHERE  1=1                       ");
		sb.append("    AND b.BusinessPartnerID IN("+ ids +")");
		//sb.append("    AND b.BusinessPartnerID = "+ businessPartnerID);
		sb.append("    AND a.CreateTime BETWEEN '"+ startTime +"' AND '"+ endTime +"'  ");
		sb.append("  ) sheetCount,                                                   ");
		
		sb.append("  (SELECT count(*) FROM Device_Terminal a INNER JOIN CRM_SalesNetwork b ON a.SalesNetworkID = b.SalesNetworkID WHERE a.IsEnabled = 1 and b.BusinessPartnerID IN ("+ ids +")) deviceCount, ");
		
		sb.append("  (SELECT sum(a.printcount) FROM Statistics_PrintRecord a INNER JOIN CRM_SalesNetwork b ");
		sb.append("   ON a.SalesNetworkID = b.SalesNetworkID ");
		sb.append("    WHERE  b.BusinessPartnerID IN ("+ ids +") AND  a.printrecordStatusID = 101 ");
		sb.append("    AND a.CreateTime BETWEEN '"+ startTime +"' AND '"+ endTime +"'  ");
		sb.append("  )printSuccNum,                                                  ");
		
		sb.append("  (SELECT sum(a.printcount) FROM Statistics_PrintRecord  a INNER JOIN CRM_SalesNetwork b ");
		sb.append("   ON a.SalesNetworkID = b.SalesNetworkID ");
		sb.append("   WHERE  b.BusinessPartnerID IN ("+ ids +") AND a.printrecordStatusID <> 101 ");
		sb.append("    AND a.CreateTime BETWEEN '"+ startTime +"' AND '"+ endTime +"' ");
		sb.append("  )cancePrintNum,     ");
		
		sb.append("  (select count(*) from  Device_Terminal a INNER JOIN CRM_SalesNetwork b ON a.SalesNetworkID = b.SalesNetworkID  where b.BusinessPartnerID IN ("+ ids +") AND a.IsEnabled = 1 AND a.LastActiveTime < dateadd(MINUTE,-5,GETDATE())) troubleDeviceNum, ");
		sb.append("  (SELECT sum(a.RefundFee) FROM Pay_RefundOrder a  INNER JOIN Pay_PayOrder b ON a.PayOrderID = b.PayOrderID WHERE  a.RefundStatusId = 101 AND b.BusinessPartnerID IN ( "+ ids +") AND a.RefundSuccessTime BETWEEN '"+ startTime+"' AND '"+ endTime +"') refundAmt ");
		
		sb.append("   ) a ");
		log.info("sql:" + sb.toString());
		return sb.toString();
	}
	
	@SuppressWarnings("unchecked")
	public String getOrderRecord(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		String startTime = (String) param.get("startTime");
		String endTime = (String) param.get("endTime");
		
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		
		sb.append(" SELECT typeDate, Round(convert(float, isnull(sum(totalAmt), 0)) / 100, 2) AS typeCount  ");
		sb.append("  FROM (   ");
		sb.append("     SELECT  ");
		sb.append("     convert(VARCHAR(12), TradeEndTime , 23) AS typeDate , ");
		sb.append("     (totalFee-totalRefundFee) AS totalAmt  ");
		sb.append("     FROM Pay_PayOrder ");
		sb.append("     WHERE PayOrderStatusID  = 102  ");
		sb.append("     AND BusinessPartnerID IN ("+ getPartnerIds(partnerIds) +")");
		sb.append("     AND TradeEndTime BETWEEN '"+ startTime+"' AND '"+endTime+"' ");
		sb.append("    ) tt GROUP BY tt.typeDate ");
		return sb.toString();
	}
	
	@SuppressWarnings("unchecked")
	public String getOrderRefundItem(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		String startTime = (String) param.get("startTime");
		String endTime = (String) param.get("endTime");
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		
		sb.append(" SELECT typeDate, Round(convert(float, isnull(sum(RefundFee), 0)) / 100, 2) AS typeCount ");
		sb.append("  FROM ( ");
		sb.append("     SELECT  ");
		sb.append("       convert(VARCHAR(12), a.RefundSuccessTime , 23) AS typeDate, ");
		sb.append("       a.RefundFee ");
		sb.append("     FROM Pay_RefundOrder a   ");
		sb.append("     INNER JOIN Pay_PayOrder b  ");
		sb.append("     ON a.PayOrderID = b.PayOrderID ");
		sb.append("     WHERE  a.RefundStatusId = 101   ");
		sb.append("     AND b.BusinessPartnerID IN ("+getPartnerIds(partnerIds) +") ");
		sb.append("     AND a.RefundSuccessTime BETWEEN '"+ startTime+"' AND '"+ endTime +"' ");
		sb.append(" ) tt GROUP BY tt.typeDate  ");		
		return sb.toString();
	}
	@SuppressWarnings("unchecked")
	public String getPrintKindSummary(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		String startTime = (String) param.get("startTime");
		String endTime = (String) param.get("endTime");
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		
		sb.append( " SELECT printKindID as printKindId ,sum(tt.PrintCount) typeCount FROM  ");
		sb.append("    (SELECT b.PrintKindID , a.PrintCount      ");
		sb.append("     FROM Statistics_PrintRecord  a         ");
		sb.append("     INNER JOIN Statistics_PrintType b      ");
		sb.append("     ON a.PrintTypeID = b.PrintTypeID       ");
		sb.append("     INNER JOIN CRM_SalesNetwork c          ");
		sb.append("     ON a.SalesNetworkID = c.SalesNetworkID ");
		sb.append("     WHERE  1=1     ");
		sb.append("     AND c.BusinessPartnerID IN (" + getPartnerIds(partnerIds)+")");
		//sb.append("     AND a.printrecordStatusID = 101  "); 统计所有数据
		sb.append("     AND b.PrintKindID IN (801, 805, 303, 302) ");
		sb.append("     AND a.CreateTime BETWEEN '" + startTime + "' and '" + endTime+ "' ");
		sb.append("   ) tt GROUP BY tt.PrintKindID ");
		log.info("sql:" + sb.toString());
		return sb.toString();
	}
	@SuppressWarnings("unchecked")
	public String getPrintKindStatistic(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		String startTime = (String) param.get("startTime");
		String endTime = (String) param.get("endTime");
		Integer kindID = (Integer) param.get("kindID");
		Integer recordSt = (Integer) param.get("recordSt");
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		
		sb.append("   select tt.typeDate, sum(tt.PrintCount) as typeCount ");
		sb.append("    from ");
		sb.append("    (     SELECT a.PrintCount,                  ");
		sb.append("          convert(VARCHAR(12), a.CreateTime , 23) AS typeDate");
		sb.append("     FROM Statistics_PrintRecord  a         ");
		sb.append("     INNER JOIN Statistics_PrintType b      ");
		sb.append("     ON a.PrintTypeID = b.PrintTypeID       ");
		sb.append("     INNER JOIN CRM_SalesNetwork c          ");
		sb.append("     ON a.SalesNetworkID = c.SalesNetworkID  ");
		sb.append("     WHERE  1=1                             ");
		sb.append("    AND c.BusinessPartnerID IN ("+ getPartnerIds(partnerIds) +")");
		if(ObjectUtil.isEmpty(recordSt)){
			sb.append("     AND  a.printrecordStatusID = 101      ");
			sb.append("     AND b.PrintKindID = " + kindID);
		}else {
			sb.append("     AND a.printrecordStatusID = "+ recordSt);
		}
		
		sb.append("     AND a.CreateTime BETWEEN '" + startTime + "' and '" + endTime+ "' ");
		sb.append("     )tt group by tt.typeDate order by typeDate ");
		log.info("sql:" + sb.toString());
		return sb.toString();
	}
	@SuppressWarnings("unchecked")
	public String getTroubleTerminalStatistic(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		String startTime = (String) param.get("startTime");
		String endTime = (String) param.get("endTime");
		Integer terminalSt = (Integer) param.get("terminalSt");
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		
		sb.append(" SELECT   ");
		sb.append("   typeDate, count(*) AS typeCount  ");
		sb.append(" FROM  ( ");
		sb.append("   SELECT convert(VARCHAR(12), a.LastUnusualTime , 23) AS typeDate ");
		sb.append("   FROM Device_Terminal a  ");
		sb.append("   INNER JOIN CRM_SalesNetwork b          ");
		sb.append("   ON a.SalesNetworkID = b.SalesNetworkID  ");
		sb.append("   WHERE b.BusinessPartnerID IN ("+ getPartnerIds(partnerIds) +") AND a.IsEnabled = 1 ");
		sb.append("   AND  a.terminalStatusID = " + terminalSt);
		sb.append("   AND a.LastUnusualTime BETWEEN '"+ startTime +"' and '" + endTime +"' ");
		sb.append("  ) tt GROUP BY typeDate order by typeDate ");
		log.info("sql:" + sb.toString());
		return sb.toString();
	}
	
	@SuppressWarnings("unchecked")
	public String getDevicePrintRecordStatistic(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		String startTime = (String) param.get("startTime");
		String endTime = (String) param.get("endTime");
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		List<StatisticDeviceDTO> terminalS = (List<StatisticDeviceDTO>) param.get("terminalS");
		
		sb.append(" SELECT  ");
		sb.append("  terminalID as termimalID, ");
		sb.append("  PrintKindID, ");
		sb.append("  sum(PrintCount)AS printCount  ");
		sb.append(" FROM ( ");
		sb.append("    SELECT ");
		sb.append("       c.PrintKindID,");
		sb.append("       a.PrintCount,");
		sb.append("       a.terminalID ");
		sb.append("   FROM Statistics_PrintRecord a ");
		sb.append("   INNER JOIN  CRM_SalesNetwork w ");
		sb.append("   ON a.salesNetworkID = w.salesNetworkID ");
		sb.append("   INNER JOIN  Device_Terminal b ");
		sb.append("   ON a.TerminalID = b.TerminalID ");
		sb.append("   INNER JOIN  Statistics_PrintType c ");
		sb.append("   ON a.PrintTypeID = c.PrintTypeID   ");
		sb.append("   WHERE a.PrintRecordStatusID = 101  ");
		sb.append("   AND w.BusinessPartnerID IN ("+ getPartnerIds(partnerIds)+")");
		sb.append("   AND a.terminalID IN ( ");
		for(int i =0; i< terminalS.size(); i++) {
			sb.append(""+terminalS.get(i).getTermimalID());
			if(i != (terminalS.size() - 1)) sb.append(", ");
		}
		sb.append("   ) "); 
		sb.append("   AND c.PrintKindID IN (801, 303, 302, 805, 301) ");
		sb.append("   AND a.Createtime BETWEEN '"+ startTime +"' and '"+ endTime +"'  ");
		sb.append("  )tt GROUP BY terminalID,PrintKindID   ");
		log.info("sql:" + sb.toString());
		return sb.toString();
	}
	
	/*public String getTerminalTotal() {
		//Integer businessPartnerID = (Integer)param.get("partnerID");
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT count(*) FROM Device_Terminal a INNER JOIN CRM_SalesNetwork b ON a.SalesNetworkID = b.SalesNetworkID WHERE IsEnabled = 1 ");
		return sb.toString();
	}*/
	@SuppressWarnings("unchecked")
	public String getTerminalSort(Map<String, Object> param){
		StringBuffer sb = new StringBuffer();
		String startTime = (String) param.get("startTime");
		String endTime = (String) param.get("endTime");
		Integer pageSize = (Integer)param.get("pageSize");
		Integer page = (Integer) param.get("page");
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		
		sb.append("select top "+ pageSize);
		sb.append(" * FROM (");
		sb.append("  select ");
		sb.append("    ROW_NUMBER() OVER (ORDER BY printCount desc) AS RN, * ");
		sb.append("    FROM ( ");
		sb.append("         SELECT   ");
		sb.append("         a.TerminalID as termimalID, ");
		//sb.append("         a.terminalCode, ");
		sb.append("         c.SalesNetworkName, ");
		sb.append("         sum(b.PrintCount) AS printCount ");
		sb.append("         FROM Device_Terminal a  ");
		sb.append("         INNER JOIN CRM_SalesNetwork c");
		sb.append("         ON a.SalesNetworkID = c.SalesNetworkID ");
		sb.append("         LEFT JOIN  Statistics_PrintRecord b      ");
		sb.append("         ON a.TerminalID = b.TerminalID ");
		sb.append("         AND b.PrintRecordStatusID = 101 ");
		if(!ObjectUtil.isEmpty(startTime) || !ObjectUtil.isEmpty(endTime)) {
			sb.append("     AND b.Createtime BETWEEN '"+ startTime +"' and '"+ endTime +"'  ");
		}
		sb.append("      WHERE a.IsEnabled = 1 ");
		sb.append("      AND  c.BusinessPartnerID IN (" + getPartnerIds(partnerIds)+")");
		sb.append("      GROUP BY a.TerminalID,  c.SalesNetworkName ");
		sb.append(" )t  )t WHERE RN > "+((page-1) * pageSize));
		log.info("sql:" + sb.toString());
		return sb.toString();
	}
	
	@SuppressWarnings("unchecked")
	public String getSaleNetworkTotal(Map<String, Object> param) {
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT count(*) FROM CRM_SalesNetwork WHERE IsEnabled = 1 AND BusinessPartnerId IN  ("+ getPartnerIds(partnerIds) +") ");
		return sb.toString();
	}
	
	public String getSaleNetworkStatisticPage(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		String startTime = (String) param.get("startTime");
		String endTime = (String) param.get("endTime");
		Integer pageSize = (Integer)param.get("pageSize");
		Integer page = (Integer) param.get("page");
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		
		sb.append("select top "+ pageSize);
		sb.append(" * FROM (");
		sb.append("  select ");
		sb.append("    ROW_NUMBER() OVER (ORDER BY printCount desc) AS RN, * ");
		sb.append("    FROM ( ");
		sb.append("         SELECT   ");
		sb.append("         a.SalesNetworkID as termimalID, ");
		sb.append("         a.SalesNetworkName  ,  ");
		sb.append("         sum(b.PrintCount) AS printCount ");
		
		sb.append("         FROM CRM_SalesNetwork a    ");
		sb.append("           LEFT JOIN  Statistics_PrintRecord b     ");
		sb.append("           ON a.SalesNetworkID = b.SalesNetworkID     ");
		sb.append("           AND b.PrintRecordStatusID = 101  ");
		if(!ObjectUtil.isEmpty(startTime) || !ObjectUtil.isEmpty(endTime)) {
			sb.append("     AND b.Createtime BETWEEN '"+ startTime +"' and '"+ endTime +"'  ");
		}
		sb.append("      WHERE a.IsEnabled = 1 ");
		sb.append("      AND  a.BusinessPartnerID IN (" + getPartnerIds(partnerIds)+")");
		sb.append("      GROUP BY a.SalesNetworkID, a.SalesNetworkName   ");
		sb.append(" )t  )t WHERE RN > "+((page-1) * pageSize));
		log.info("sql:" + sb.toString());
		return sb.toString();
		
	}
	
	
	@SuppressWarnings("unchecked")
	public String getTerminalTotal(Map<String, Object> param){
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT count(*) FROM Device_Terminal a INNER JOIN CRM_SalesNetwork b ON a.SalesNetworkID = b.SalesNetworkID  WHERE a.IsEnabled = 1  ");
		sb.append(" AND b.BusinessPartnerID IN ("+getPartnerIds(partnerIds)+")");
		return sb.toString();
	}
	@SuppressWarnings("unchecked")
	public String getDeviceRecordByPrintKind(Map<String, Object> param){
		
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		Integer terminalID = (Integer) param.get("terminalID");
		Integer printKind = (Integer) param.get("printKind");
		String startTime = (String) param.get("startTime");
		String endtime = (String) param.get("endTime");
		
		StringBuffer sb = new StringBuffer();
		sb.append("  SELECT   typeDate as itemName, sum(PrintCount) as  itemCount FROM ( ");
		sb.append("  SELECT a.PrintCount, convert(VARCHAR(12), a.CreateTime , 23) AS typeDate   FROM CRM_SalesNetwork w INNER JOIN  Statistics_PrintRecord a ON w.salesNetworkID = a.salesNetworkID  INNER JOIN  Statistics_PrintType b ON a.PrintTypeID = b.PrintTypeID    ");
		sb.append("  WHERE a.PrintRecordStatusID = 101 ");
		sb.append("  AND w.BusinessPartnerID IN ("+getPartnerIds(partnerIds)+")");
		sb.append("  AND a.TerminalID = "+ terminalID);
		sb.append("  AND b.PrintKindID = "+ printKind);
		sb.append("  AND a.Createtime BETWEEN '"+ startTime + "' AND '" + endtime + "' )tt  GROUP BY typeDate");
		return sb.toString();
	}
	
	@SuppressWarnings("unchecked")
	public String getSaleNetworkRecordStatistic(Map<String, Object> param) {
		StringBuffer sb = new StringBuffer();
		String startTime = (String) param.get("startTime");
		String endTime = (String) param.get("endTime");
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		List<StatisticDeviceDTO> saleNetworkIDs = (List<StatisticDeviceDTO>) param.get("saleNetworkIDs");
		
		sb.append(" SELECT  ");
		sb.append("   SalesNetworkID as termimalID ,  ");
		sb.append("  PrintKindID, ");
		sb.append("  sum(PrintCount)AS printCount  ");
		sb.append(" FROM ( ");
		sb.append("    SELECT ");
		sb.append("       c.PrintKindID,");
		sb.append("       a.PrintCount,");
		sb.append("      w.SalesNetworkID    ");
		sb.append("   FROM Statistics_PrintRecord a ");
		sb.append("   INNER JOIN  CRM_SalesNetwork w ");
		sb.append("   ON a.salesNetworkID = w.salesNetworkID ");
		sb.append("   INNER JOIN  Statistics_PrintType c ");
		sb.append("   ON a.PrintTypeID = c.PrintTypeID   ");
		sb.append("   WHERE a.PrintRecordStatusID = 101  ");
		sb.append("   AND w.BusinessPartnerID IN ("+ getPartnerIds(partnerIds)+")");
		sb.append("   AND w.salesNetworkID IN ( ");
		for(int i =0; i< saleNetworkIDs.size(); i++) {
			sb.append(""+saleNetworkIDs.get(i).getTermimalID());
			if(i != (saleNetworkIDs.size() - 1)) sb.append(", ");
		}
		sb.append("   ) "); 
		sb.append("   AND c.PrintKindID IN (801, 303, 302, 805, 301) ");
		sb.append("   AND a.Createtime BETWEEN '"+ startTime +"' and '"+ endTime +"'  ");
		sb.append("  )tt GROUP BY SalesNetworkID,PrintKindID   ");
		log.info("sql:" + sb.toString());
		return sb.toString();
	}
	
	@SuppressWarnings("unchecked")
	public String getSalsNetworkRecordByPrintKind(Map<String, Object> param){
		
		List<Integer> partnerIds = (List<Integer>)param.get("partnerIds");
		Integer terminalID = (Integer) param.get("terminalID");
		Integer printKind = (Integer) param.get("printKind");
		String startTime = (String) param.get("startTime");
		String endtime = (String) param.get("endTime");
		
		StringBuffer sb = new StringBuffer();
		sb.append("  SELECT   typeDate as itemName, sum(PrintCount) as  itemCount FROM ( ");
		sb.append("  SELECT a.PrintCount, convert(VARCHAR(12), a.CreateTime , 23) AS typeDate  ");
		sb.append("   FROM CRM_SalesNetwork w ");
		sb.append("  INNER JOIN  Statistics_PrintRecord a ");
		sb.append("  ON w.salesNetworkID = a.salesNetworkID    ");
		sb.append("  INNER JOIN  Statistics_PrintType b  ");
		sb.append("  ON a.PrintTypeID = b.PrintTypeID ");
		sb.append("  WHERE a.PrintRecordStatusID = 101 ");
		sb.append("  AND w.BusinessPartnerID IN ("+getPartnerIds(partnerIds)+")");
		sb.append("  AND a.salesNetworkID = "+ terminalID);
		sb.append("  AND b.PrintKindID = "+ printKind);
		sb.append("  AND a.Createtime BETWEEN '"+ startTime + "' AND '" + endtime + "' )tt  GROUP BY typeDate");
		return sb.toString();
	}
	
}
